﻿using Apps.Common;
using Apps.Models;
using Apps.Models.CustomModels;
using Microsoft.SqlServer.Server;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Apps.DAL.SelfRcRelated
{
    public partial class SelfRcRelated_Repository : DbContext<SELF_SAMPLING_RC_RELATED>
    {
        /// <summary>
        /// 获取自采煤相关信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public TestDataOutPutModel GetSelfSampingInfo(TestDataQuertParaModel model)
        {
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            string SQL = @"SELECT Distinct A.BATCH_CODE,C.TEST_CODE, A.BATCH_TYPE, A.TYPE_STR, C.CRI,C.CSR,D.ITEM_VALUE YANX_VALUE,A.UPDATE_TIME
                            FROM V_STATS_LMS_PLAN_RESULT A 
                            LEFT JOIN (
                                SELECT BB.BATCH_CODE,BB.ITEM_VALUE FROM (
                                SELECT BATCH_CODE,MAX(ADDTIME) ADDTIME FROM YANX_RECORD GROUP BY BATCH_CODE) AA 
                                INNER JOIN 
                                YANX_RECORD BB ON AA.BATCH_CODE = BB.BATCH_CODE AND AA.ADDTIME = BB.ADDTIME AND AUDIT_STATUS = '审核通过'
                            ) D ON A.BATCH_CODE = D.BATCH_CODE
                            LEFT JOIN V_LMS_RESULT C ON A.TEST_CODE = C.TEST_CODE
                            WHERE C.TEST_STATE = 1 AND C.TEST_TYPE_STR = '最终指标值' AND APPROVAL_STATUS = 3
                            
                            
                            AND A.TEST_CODE = @TestCode
                            
                            AND ROWNUM = 1
                            ORDER BY A.BATCH_CODE DESC,A.UPDATE_TIME DESC"
            ;
            //AND BATCH_TYPE = '自采煤'
            //AND CRI <> 0 AND CSR <> 0
            //AND AUDIT_STATE = '已通过'
            //AND AUDIT_REMARK = '同意'
            //AND (TYPE_STR = '小焦炉煤' OR TYPE_STR = '小焦炉焦炭')


            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@TestCode", model.TestCode));
            var data = db.Ado.GetDataTable(SQL, sugarParameters);
            if (data != null)
            {
                if (data.Rows.Count > 0)
                {
                    return data.ToDataList<TestDataOutPutModel>().First();
                }
            }
            return new TestDataOutPutModel();
        }

        /// <summary>
        ///入厂关联自采
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool RelaterdData(TestDataOutPutModel model)
        {
            List<string> ids = model.dataIds.Split(',').ToList();
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            foreach (var item in ids)
            {
                if (!string.IsNullOrEmpty(item))
                {
                    var any = db.Queryable<SELF_SAMPLING_RC_RELATED>().Where(m => m.RC_BATCH_CODE == item).Count() > 0;
                    if (any)
                    {
                        //update
                        db.Updateable<SELF_SAMPLING_RC_RELATED>(new SELF_SAMPLING_RC_RELATED
                        {
                            CSR = model.CSR,
                            CRI = model.CRI,
                            YX_VALUE = model.YANX_VALUE,
                            RC_BATCH_CODE = item,
                            XJLJT_CODE = model.XJLJT_CODE,
                            XJLM_CODE = model.XJLM_CODE,
                            ADDTIME = DateTime.Now,
                            //SELF_BATCH_CODE = model.BATCH_CODE,
                            ISRELATED = "是"
                        }).UpdateColumns(m => new { m.CRI, m.CSR, m.YX_VALUE, m.XJLM_CODE, m.XJLJT_CODE, m.ISRELATED }).Where(m => m.RC_BATCH_CODE == item)
                            .ExecuteCommand();
                    }
                    else
                    {
                        //insert
                        db.Insertable<SELF_SAMPLING_RC_RELATED>(new SELF_SAMPLING_RC_RELATED
                        {
                            ID = System.Guid.NewGuid().ToString(),
                            CSR = model.CSR,
                            CRI = model.CRI,
                            YX_VALUE = model.YANX_VALUE,
                            XJLJT_CODE = model.XJLJT_CODE,
                            XJLM_CODE = model.XJLM_CODE,
                            RC_BATCH_CODE = item,
                            ADDTIME = DateTime.Now,
                            //SELF_BATCH_CODE = model.BATCH_CODE,
                            ISRELATED = "是"
                            //SELF_TESTCODE = model.TEST_CODE
                        }).InsertColumns(m => new { m.RC_BATCH_CODE, m.ISRELATED, m.CSR, m.CRI, m.XJLM_CODE, m.XJLJT_CODE, m.YX_VALUE, m.ADDTIME, m.ID }).ExecuteCommand();
                    }
                }
            }
            return true;
        }

        /// <summary>
        ///取消入厂关联自采
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UnRelaterdData(TestDataOutPutModel model)
        {
            List<string> ids = model.dataIds.Split(',').ToList();
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            foreach (var item in ids)
            {
                //db.Deleteable<SELF_SAMPLING_RC_RELATED>().Where(m => m.RC_BATCH_CODE == item).ExecuteCommand();
                db.Updateable<SELF_SAMPLING_RC_RELATED>(new SELF_SAMPLING_RC_RELATED
                {
                    CSR = "",
                    CRI = "",
                    YX_VALUE = "",
                    XJLM_CODE = "",
                    XJLJT_CODE = "",
                    ISRELATED = ""
                }).UpdateColumns(m => new { m.CRI, m.CSR, m.YX_VALUE, m.XJLJT_CODE, m.XJLM_CODE, m.ISRELATED }).Where(m => m.RC_BATCH_CODE == item).ExecuteCommand();
            }
            return true;
        }

        /// <summary>
        ///设置为结算状态
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool JieSuanData(TestDataOutPutModel model)
        {
            List<string> ids = model.dataIds.Split(',').ToList();
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            foreach (var item in ids)
            {
                if (!string.IsNullOrEmpty(item))
                {
                    var any = db.Queryable<SELF_SAMPLING_RC_RELATED>().Where(m => m.RC_BATCH_CODE == item).Count() > 0;
                    if (any)
                    {
                        //update
                        db.Updateable<SELF_SAMPLING_RC_RELATED>(new SELF_SAMPLING_RC_RELATED
                        {
                            ISJSUAN = "是"
                        }).UpdateColumns(m => new { m.ISJSUAN }).Where(m => m.RC_BATCH_CODE == item).ExecuteCommand();
                    }
                    else
                    {
                        //insert
                        db.Insertable<SELF_SAMPLING_RC_RELATED>(new SELF_SAMPLING_RC_RELATED
                        {
                            ID = System.Guid.NewGuid().ToString(),
                            RC_BATCH_CODE = item,
                            ADDTIME = DateTime.Now,
                            ISJSUAN = "是"
                        }).InsertColumns(m => new { m.RC_BATCH_CODE, m.ISJSUAN, m.ADDTIME, m.ID }).ExecuteCommand();
                    }
                }
            }
            return true;
        }

        /// <summary>
        ///取消结算状态
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UnJieSuanData(TestDataOutPutModel model)
        {
            List<string> ids = model.dataIds.Split(',').ToList();
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            foreach (var item in ids)
            {
                db.Updateable<SELF_SAMPLING_RC_RELATED>(new SELF_SAMPLING_RC_RELATED
                {
                    ISJSUAN = ""
                }).UpdateColumns(m => new { m.ISJSUAN }).Where(m => m.RC_BATCH_CODE == item).ExecuteCommand();
            }
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SELF_SAMPLING_RC_RELATED Info(string batchCode, string type = "")
        {
            //if (type == "RC")
            return Db.Queryable<SELF_SAMPLING_RC_RELATED>().Where(m => m.RC_BATCH_CODE == batchCode).First();
            // return Db.Queryable<SELF_SAMPLING_RC_RELATED>().Where(m => m.XJLJTTEST_CODE == batchCode).First();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public V_LMS_RESULT TestDataInfo(string testCode)
        {
            return Db.Queryable<V_LMS_RESULT, V_STATS_LMS_PLAN_RESULT>((o, i) => new JoinQueryInfos(
                                            JoinType.Inner, o.TEST_CODE == i.TEST_CODE //左连接 左链接 左联 
                                        )).Where(m => m.TEST_CODE == testCode).First();
        }

        /// <summary>
        /// 获取入厂批次列表
        /// </summary>
        /// <param name="model"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<RcBatchModel> GetRcBatchList(TestDataQuertParaModel model, ref int totalCount)
        {
            //string SQL = @" SELECT Distinct BATCH_CODE,MATERIEL_NAME,MINE_NAME,SUPPLIER_NAME,TRANSPORT_NAME,CRI,CSR,YX_VALUE,SELF_BATCH_CODE FROM V_PL_RC_DETAILS A LEFT JOIN SELF_SAMPLING_RC_RELATED B
            //                ON A.BATCH_CODE = B.RC_BATCH_CODE
            //                ORDER BY BATCH_CODE DESC";

            //SELECT* FROM
            //                                            (
            //                                                SELECT Distinct BATCH_CODE, BATCH_TYPE, MATERIEL_NAME, NVL(TYPE_STR,'入厂煤') TYPE_STR FROM V_STATS_LMS_PLAN_RESULT
            //                                                WHERE NVL(TYPE_STR, '入厂煤') <> '小焦炉煤' AND NVL(TYPE_STR，'入厂煤') <> '小焦炉焦炭'

            //		) ORDER BY BATCH_CODE DESC

            return Db.Queryable<V_STATS_LMS_PLAN_RESULT, SELF_SAMPLING_RC_RELATED, LMS_COLLECTSAMPLE, V_STATS_LMS_PLAN_RESULT, V_LMS_RESULT>((AA, BB, CC, DD, EE) => new JoinQueryInfos(
                                              JoinType.Left, AA.BATCH_CODE == BB.RC_BATCH_CODE,
                                              JoinType.Left, AA.BATCH_CODE == CC.BATCH_CODE,
                                              JoinType.Left, AA.BATCH_CODE == DD.BATCH_CODE,
                                              JoinType.Left, DD.TEST_CODE == EE.TEST_CODE

                                          ))
                                          .WhereIF(!string.IsNullOrEmpty(model.BatchCode), (AA, BB, CC, DD, EE) => AA.BATCH_CODE.Contains(model.BatchCode))
                                          .WhereIF(!string.IsNullOrEmpty(model.MaterielName), (AA, BB, CC, DD, EE) => AA.MATERIEL_NAME.Contains(model.MaterielName))
                                          .WhereIF(model.ISJSUAN == "是", (AA, BB, CC, DD, EE) => BB.ISJSUAN == "是")
                                          .WhereIF(model.ISRELATED == "是", (AA, BB, CC, DD, EE) => BB.ISRELATED == "是")
                                          .WhereIF(model.ISJSUAN == "否", (AA, BB, CC, DD, EE) => BB.ISJSUAN != "是")
                                          .WhereIF(model.ISRELATED == "否", (AA, BB, CC, DD, EE) => BB.ISRELATED != "是")
                                          .WhereIF(!string.IsNullOrEmpty(model.MaterielName), (AA, BB, CC, DD, EE) => AA.MATERIEL_NAME.Contains(model.MaterielName))
                                          .Where((AA, BB, CC, DD, EE) => SqlFunc.IsNull(AA.TYPE_STR, "入厂煤") != "小焦炉煤")


                                          .Select((AA, BB, CC, DD, EE) => new RcBatchModel
                                          {
                                              BATCH_TYPE = AA.BATCH_TYPE,
                                              BATCH_CODE = AA.BATCH_CODE,
                                              CRI = BB.CRI,
                                              CSR = BB.CSR,
                                              MATERIEL_NAME = AA.MATERIEL_NAME,
                                              MINE_NAME = AA.TYPE_STR,
                                              XJLMTEST_CODE = BB.XJLM_CODE,
                                              XJLJTTEST_CODE = BB.XJLJT_CODE,
                                              ISJSUAN = BB.ISJSUAN,
                                              COLLECT_TIME = CC.COLLECT_TIME,
                                              //SUPPLIER_NAME = AA.SUPPLIER_NAME,
                                              //TRANSPORT_NAME = AA.TRANSPORT_NAME,
                                              YX_VALUE = BB.YX_VALUE,
                                              ADDTIME = SqlFunc.IsNullOrEmpty(BB.ADDTIME) ? "" : BB.ADDTIME.ToString("yyyy-MM-dd HH:mm:ss"),
                                              //ISRelated = !SqlFunc.IsNullOrEmpty(BB.SELF_BATCH_CODE) ? "已关联" : ""
                                              //
                                              MT = DD.MT.ToString(),
                                              MAD = DD.MAD.ToString(),
                                              AD = DD.AD.ToString(),
                                              VDAF = DD.VDAF.ToString(),
                                              STD = DD.STD.ToString(),
                                              G = DD.G.ToString(),
                                              Y = DD.Y.ToString(),
                                              X = DD.X.ToString(),
                                              A = DD.A.ToString(),
                                              B = DD.B.ToString(),
                                              QNETAR_KCAL = DD.QNETAR_KCAL.ToString(),
                                              SMALL = EE.SMALL.ToString(),
                                              MEDIUM = EE.MEDIUM.ToString(),
                                              LARGE = EE.LARGE.ToString(),
                                              M40 = EE.M40.ToString(),
                                              M10 = EE.M10.ToString(),
                                              JTXXLARGE = EE.JTXXLARGE.ToString(),
                                              JTXLARGE = EE.JTXLARGE.ToString(),
                                              JTLARGE = EE.JTLARGE.ToString(),
                                              JTMEDIUM = EE.JTMEDIUM.ToString(),
                                              JTSMALL = EE.JTSMALL.ToString(),
                                              SELF_CRI = EE.CRI.ToString(),
                                              SELF_CSR = EE.CSR.ToString(),
                                          })
                                           .Mapper(AA => AA.SELF_YANX_VALUE = Db.Ado.GetString(@"SELECT BB.ITEM_VALUE FROM (
	                                                                    SELECT BATCH_CODE,MAX(ADDTIME) ADDTIME FROM YANX_RECORD GROUP BY BATCH_CODE) AA 
	                                                                    INNER JOIN 
	                                                                    YANX_RECORD BB ON AA.BATCH_CODE = BB.BATCH_CODE AND AA.ADDTIME = BB.ADDTIME AND AUDIT_STATUS = '审核通过'"))
                                          .OrderBy((AA) => SqlFunc.ToInt64(AA.BATCH_CODE), OrderByType.Desc)
                                          .Distinct().OrderBy(AA => AA.BATCH_CODE, OrderByType.Desc).ToPageList(model.PageIndex, model.PageSize, ref totalCount);//



            return Db.Queryable<V_PL_RC_DETAILS, SELF_SAMPLING_RC_RELATED>((AA, BB) => new JoinQueryInfos(
                                              JoinType.Left, AA.BATCH_CODE == BB.RC_BATCH_CODE
                                          ))
                                          .WhereIF(!string.IsNullOrEmpty(model.BatchCode), (AA, BB) => AA.BATCH_CODE.Contains(model.BatchCode))
                                          .WhereIF(!string.IsNullOrEmpty(model.MaterielName), (AA, BB) => AA.MATERIEL_NAME.Contains(model.MaterielName))
                                          .OrderBy((AA, BB) => SqlFunc.ToInt64(AA.BATCH_CODE), OrderByType.Desc)
                                          .Select((AA, BB) => new RcBatchModel
                                          {
                                              BATCH_CODE = AA.BATCH_CODE,
                                              CRI = BB.CRI,
                                              CSR = BB.CSR,
                                              MATERIEL_NAME = AA.MATERIEL_NAME,
                                              MINE_NAME = AA.MINE_NAME,
                                              SUPPLIER_NAME = AA.SUPPLIER_NAME,
                                              TRANSPORT_NAME = AA.TRANSPORT_NAME,
                                              YX_VALUE = BB.YX_VALUE,
                                              ADDTIME = SqlFunc.IsNullOrEmpty(BB.ADDTIME) ? "" : BB.ADDTIME.ToString("yyyy-MM-dd HH:mm:ss"),
                                              //ISRelated = !SqlFunc.IsNullOrEmpty(BB.SELF_BATCH_CODE) ? "已关联" : ""
                                          }).OrderBy(AA => AA.BATCH_CODE, OrderByType.Desc).ToPageList(model.PageIndex, model.PageSize, ref totalCount);//
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        //public bool Any(string selfBacthCode, string rcBatchCode)
        //{
        //    return Db.Queryable<SELF_SAMPLING_RC_RELATED>().Where(m => m.SELF_BATCH_CODE == selfBacthCode && m.RC_BATCH_CODE == rcBatchCode).Any();
        //}


        /// <summary>
        /// 
        /// </summary>
        /// <param name="queryStr"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortFiled"></param>
        /// <param name="orderBy"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<SELF_SAMPLING_RC_RELATED> GetList(string batchCode, string testCode, string name, int pageIndex, int pageSize, string sortFiled, string orderBy, ref int totalCount)
        {
            var exp = Expressionable.Create<SELF_SAMPLING_RC_RELATED>();
            exp.AndIF(!string.IsNullOrEmpty(batchCode), it => it.RC_BATCH_CODE.Contains(batchCode));
            exp.AndIF(!string.IsNullOrEmpty(testCode), it => it.XJLJT_CODE.Contains(testCode));
            exp.AndIF(!string.IsNullOrEmpty(testCode), it => it.XJLM_CODE.Contains(testCode));
            return Db.Queryable<SELF_SAMPLING_RC_RELATED>().OrderBy(sortFiled + " " + orderBy).Where(exp.ToExpression()).ToPageList(pageIndex, pageSize, ref totalCount);
        }


        /// <summary>
        /// 根据制样码获取批次码
        /// </summary>
        /// <returns></returns>
        public string GetBatchCode(string testCode)
        {
            return Db.Ado.GetString("SELECT MAX(BATCH_CODE) BATCH_CODE FROM LMS_COLLECTSAMPLE WHERE TEST_CODE = @TEST_CODE", new SugarParameter[] { new SugarParameter("@TEST_CODE", testCode) });
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(SELF_SAMPLING_RC_RELATED model)
        {
            return Db.Insertable<SELF_SAMPLING_RC_RELATED>(model).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(SELF_SAMPLING_RC_RELATED model)
        {
            return Db.Updateable<SELF_SAMPLING_RC_RELATED>(model).UpdateColumns(m => new
            {
                m.RC_BATCH_CODE,
                m.ADDTIME,
            }).Where(m => m.ID == model.ID).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(string id)
        {
            return Db.Deleteable<SELF_SAMPLING_RC_RELATED>().Where(m => m.ID == id).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<TestDataCokeOutPutModel> GetCokeList(TestDataCokeParaModel model, ref int totalCount, bool isPage = true)
        {
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            string SQL = $@"SELECT to_char(INSTRUMENT) AS INSTRUMENT,
                               AUTO_NO,
                               TEST_CODE,
                               SAMPLE_WEIGHT,
                               SAMPLE_COUNT,
                               CRI_WEIGHT,
                               CRI_COUNT,
                               CSR_WEIGHT,
                               CSR_COUNT,
                               CRI,
                               CSR,
                               REMARK,
                               B.TRUENAME AS TEST_USER,
                               TO_CHAR(TEST_TIME,'YYYY-MM-DD HH24:MI:SS') AS TEST_TIME
                          FROM LMS_TESTDATA_CRI A
                          LEFT JOIN SYSUSER B
                          ON A.TEST_USER = B.ID
                          WHERE 1 = 1 ";
            //LEFT JOIN LMS_COLLECTSAMPLE F ON A.BATCH_CODE = F.BATCH_CODE

            if (!string.IsNullOrEmpty(model.TestCode))
            {
                SQL += " AND TEST_CODE = @TEST_CODE";
            }
            if (!string.IsNullOrEmpty(model.StartDate))
            {
                DateTime dStartDate = DateTime.Parse(model.StartDate);//.Date; 
                SQL += $" AND TEST_TIME >= TO_DATE('{dStartDate.ToString("yyyy-MM-dd 00:00:00")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }
            if (!string.IsNullOrEmpty(model.EndDate))
            {
                DateTime dEndDate = DateTime.Parse(model.EndDate);//.Date.AddDays(1);
                SQL += $" AND TEST_TIME <= TO_DATE('{dEndDate.ToString("yyyy-MM-dd 23:59:59")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }
            if (!string.IsNullOrEmpty(model.Instrument))
            {
                SQL += " AND A.INSTRUMENT = @INSTRUMENT";
            }

            string SQLTOTAL = $@"SELECT COUNT(1) FROM (
								    %WHERE%
								)";
            SQLTOTAL = SQLTOTAL.Replace("%WHERE%", SQL);
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@TEST_CODE", model.TestCode));
            sugarParameters.Add(new SugarParameter("@INSTRUMENT", model.Instrument));
            if (isPage)
                totalCount = db.Ado.GetInt(SQLTOTAL, sugarParameters);
            else
                totalCount = 0;
            string SQLPAGE = $@"SELECT * FROM (
								    SELECT row_number() OVER (ORDER BY TEST_TIME DESC) AS row_num,T.* FROM 
								    (
                                        %WHERE%
								    ) T ORDER BY TEST_TIME DESC
								)
								WHERE row_num BETWEEN {(model.PageIndex <= 1 ? 1 : (model.PageIndex - 1) * model.PageSize)} AND {model.PageIndex * model.PageSize} ";
            SQLPAGE = SQLPAGE.Replace("%WHERE%", SQL);
            System.Data.DataTable data = null;
            if (isPage)
                data = db.Ado.GetDataTable(SQLPAGE, sugarParameters);
            else
                data = db.Ado.GetDataTable(SQL + " ORDER BY TEST_TIME DESC", sugarParameters);
            return data.ToDataList<TestDataCokeOutPutModel>();
        }

    }
}
